﻿
--SELECT * FROM TBL_ACCOUNT_PLANNING

DELETE FROM TBL_ACCOUNT_PLANNING

INSERT TBL_ACCOUNT_PLANNING
SELECT * FROM [SERVER12].[SMECUSTOMER360].DBO.TBL_ACCOUNT_PLANNING

SELECT *,  
	(CASE WHEN INTERNAL_AMOUNT LIKE'%B%'THEN REPLACE(INTERNAL_AMOUNT,'B','000000000')
	WHEN INTERNAL_AMOUNT LIKE'%M%'THEN REPLACE(INTERNAL_AMOUNT,'M','000000') 
	ELSE INTERNAL_AMOUNT END)AS INTENRNAL_AMOUNT_QD 
INTO #LIMIT_FILTER FROM [SERVER74].STAGING.[DBO].[DLYTB_LIMIT_ORG] 
WHERE ID LIKE '%0050000%' AND INTERNAL_AMOUNT IS NOT NULL

SELECT SUM(CONVERT(FLOAT, INTENRNAL_AMOUNT_QD)) AS LIMIT_CIF, LIABILITY_NUMBER INTO #LIMIT_SUM_CIF 
FROM #LIMIT_FILTER 
GROUP BY LIABILITY_NUMBER

SELECT * INTO #LIMIT_SUM_CIF_50TY FROM #LIMIT_SUM_CIF WHERE LIMIT_CIF > 50000000000 ORDER BY LIABILITY_NUMBER

IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'CUSTOMER_LIMIT_50')
        DROP TABLE CUSTOMER_LIMIT_50

SELECT B.* INTO CUSTOMER_LIMIT_50
FROM #LIMIT_SUM_CIF_50TY AS A, TBL_CUSTOMER AS B
WHERE A.LIABILITY_NUMBER=B.CIF

SELECT CIF INTO #CIF_TAM 
FROM CUSTOMER_LIMIT_50 
WHERE CIF IN (SELECT CIF FROM  [SERVER12].[ANHCP].[DBO].[GIAI_NGAN_TRADE_LOAN])
OR CIF IN (SELECT CIF FROM  [SERVER12].[ANHCP].[DBO].[DOANH_SO_TTR_LC_DP])

DELETE FROM CIF_ACCOUNT_PLANNING

INSERT CIF_ACCOUNT_PLANNING
SELECT DISTINCT CIF  FROM #CIF_TAM

INSERT TBL_ACCOUNT_PLANNING 
SELECT A.CIF, A.CUS_NAME, A.INDUSTRY_NAME, 
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, NULL, A.DAO, NULL, NULL, NULL,NULL,NULL, 0
FROM CUSTOMER_LIMIT_50 AS A, CIF_ACCOUNT_PLANNING AS B 
WHERE A.CIF= B.CIF AND A.CIF NOT IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING )

UPDATE A SET A.DAO_NAME=B.DAO_NAME,
 A.BRANCH_CODE_SME=B.BRANCH_CODE_SME, 
 A.REGION=C.REGION, A.BRANCH_NAME=C.BRANCH_NAME_SME
FROM TBL_ACCOUNT_PLANNING AS A,TBL_HR AS B, TBL_BRANCH AS C 
WHERE A.DAO_CIF = B.DAO AND B.BRANCH_CODE_SME=C.BRANCH_CODE_SME


select * from TBL_ACCOUNT_PLANNING